﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.Mail;
using System.Text;
using CRMFramework;
using Newtonsoft.Json;
using System.Web;
using System.IO;


namespace CRMBiz
{
    public static class SMSTemplateHandler
    {
        /// <summary>
        /// function to bind sms Template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        public static DataTable BindSMSTemplate()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT * FROM crm_smstemplate";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch(Exception)
            {
                throw;
            }
            return dt;
        }
        /// <summary>
        /// function to Insert sms Template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        public static int AddSMSTemplate(string SMSTemplate, string TemplateFormat, int CategoryID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@SMSTemplate", SMSTemplate),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@CategoryId", CategoryID),
                };

                string sbSql = "Select * From crm_smstemplate Where SMSTemplate = @SMSTemplate And TemplateFormat = @TemplateFormat and  CategoryId= @CategoryId";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "insert into crm_smstemplate(SMSTemplate,TemplateFormat,CategoryId) values(@SMSTemplate,@TemplateFormat,@CategoryId)";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to Update sms Template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        public static int UpdateSMSTemplate(int SMSTemplateID, string TemplateFormat, int CategoryID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@SMSTemplateID", SMSTemplateID),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@CategoryId", CategoryID),
                };

                string sbSql = "Select * From crm_smstemplate Where SMSTemplateID <> @SMSTemplateID And TemplateFormat = @TemplateFormat and  CategoryId= @CategoryId";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    intReturnValue = 0;
                }
                else
                {
                    sbSql = "update crm_smstemplate set TemplateFormat = @TemplateFormat ,CategoryId=@CategoryId where SMSTemplateID = @SMSTemplateID";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }
        /// <summary>
        /// function to Delete sms Template
            
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        public static bool DeleteSMSTemplate(int SMSTemplateID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@SMSTemplateID", SMSTemplateID),
                };

                string sbSql = "delete from crm_smstemplate where SMSTemplateID=@SMSTemplateID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        /// <summary>
        /// function to get column name
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        public static DataTable getColumnName(string condition)
        {
            DataTable dt;
            try
            {
                string sql = "select * from view_getcolumnname where COLUMN_NAME like '%(" + condition.Trim() + ")%'";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable getColumnName1()
        {
            DataTable dt;
            try
            {
                string sql = "select * from view_getcolumnname";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataSet getAllMobileNo(int ID, int isAppID)
        {
            DataSet ds;
            try
            {
                string query = string.Empty;
                if(isAppID==1)
                    query = "select replace(crm_contact.Mobile,' ','') as Mobile , crm_contact.FirstName FROM crm_applicationcontact INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID WHERE crm_applicationcontact.app_id=" + ID;
                else
                    query = "select replace(Mobile,' ','') As Mobile, FirstName from crm_contact where ContactID=" + ID;

                ds = DBhelper.ExecuteDataset(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// function to get column name
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// 
        public static bool AddHistory(int app_id, int ContactID, string HistoryDate, string HistoryTime, string Action, string Operator, string Description)
        {
            bool flag = false;
            try
            {
                string sbSql = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@HistoryDate", HistoryDate),
                    DBhelper.SetParameter("@HistoryTime", HistoryTime),
                    DBhelper.SetParameter("@Action", Action),
                    DBhelper.SetParameter("@Operator", Operator),
                    DBhelper.SetParameter("@Description", Description),
                };

                if (app_id != 0)
                    sbSql = "insert into crm_history(app_id, HistoryDate, HistoryTime, Action, Operator, Description) values(@app_id, STR_TO_DATE(@HistoryDate,'%m/%d/%Y'), @HistoryTime, @Action, @Operator, @Description)";
                else
                    sbSql = "insert into crm_contacthistory(ContactID, HistoryDate, HistoryTime, Action, Operator, Description) values(@ContactID, STR_TO_DATE(@HistoryDate,'%m/%d/%Y'), @HistoryTime, @Action, @Operator, @Description)";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
